﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Windows.Forms;
using DevComponents.DotNetBar.Controls;
using KnowledgeNotebook.BLL;
using System.Data;


namespace KnowledgeNotebook.DAL
{
    public class DatabaseOption:GetParameters
    {
        public int InsertData(string title,string detail)
        {
            SqlParameter[] sqlParameters = base.GetInsertParameters(title, detail);
            int code=base.ExecuteProcedure("usp_InsertData",sqlParameters);
            return code;
        }

        public int UpdateData(int id,string title,string detail)
        {
            SqlParameter[] sqlParameters = base.GetUpdateParameters(id,title,detail);
            int code=base.ExecuteProcedure("usp_UpdateData",sqlParameters);
            return code;
        }

        public DataTable SearchData(string title)
        {
            SqlParameter[] sqlparameters = base.GetSearchParameters(title);
           // base.ExecuteProcedure("usp_SearchData",sqlparameters);
            DataTable dt = SelectData("usp_SearchData", sqlparameters);
            return dt;
        }

        public void DeleteData(int id)
        {
            SqlParameter[] sqlParameters=base.GetDeleteParameters(id);
            base.ExecuteProcedure("usp_DeleteData",sqlParameters);
        }

        public void DeleteMultiData(string sql)
        {
            ExecuteNotSelectSql(sql);}

        

        public List<string> GetTables(string databaseName)
        {
            List<string> tables=new List<string>();
            string sql = "SELECT b.Name+'.'+a.name as name FROM " + databaseName + 
                ".sys.tables as a inner join "+databaseName+".sys.schemas as b on a.schema_id=b.schema_id order by name";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable!=null && dataTable.Rows.Count>0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    tables.Add(dataTable.Rows[i][0].ToString());
                }

                return tables;
            }
            return null;
        }


        public List<string> GetFields(string table)
        {
            List<string > fields=new List<string>();
            string sql = "SELECT name FROM sys.columns WHERE object_id=Object_Id('" + table + "') order by name";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    fields.Add(dataTable.Rows[i][0].ToString());
                }

                return fields;
            }
            return null;
        }


        public List<string> GetProcedures(string database)
        {
            List<string> procedures = new List<string>();
            string sql = "SELECT b.name+'.'+a.Name as name FROM " + database + ".sys.procedures AS a INNER JOIN sys.schemas AS b ON a.schema_id = b.schema_id order by name";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    procedures.Add(dataTable.Rows[i][0].ToString());
                }

                return procedures;
            }
            return null;
        }
                
        public List<string> GetSchemas(string database)
        {
            List<string> schemas = new List<string>();
            string sql = "select name from " + database + ".sys.schemas order by name";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    schemas.Add(dataTable.Rows[i][0].ToString());
                }

                return schemas;
            }
            return null;
        }

        public List<string> GetProcParameters(string procedurename)
        {
            List<string> procParameters = new List<string>();
            string sql = "select   a.name,b.name+'('+CONVERT(VARCHAR(5),a.length)+')' from   syscolumns   a   join   systypes   b   on   a.xtype=b.xusertype where   object_id( '"+procedurename+"')=id order by a.name";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    procParameters.Add(dataTable.Rows[i][0] + " " + dataTable.Rows[i][1]);
                }

                return procParameters;
            }
            return null;
        }

        public List<string> GetViews(string database)
        {
            List<string> views = new List<string>();
            string sql = "SELECT Name FROM " + database + ".sys.views order by name";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    views.Add(dataTable.Rows[i][0].ToString());
                }

                return views;
            }
            return null;
        }


        public List<string> GetKeys(string table)
        {
            List<string> keys = new List<string>();
           // string sql = "SELECT name, CASE XTYPE WHEN 'PK' THEN '(PK)' WHEN 'F' THEN '(FK)' END AS k FROM " + database + "..sysobjects WHERE xtype IN ('PK','F')";
            string sql =
                "SELECT Name FROM sysobjects WHERE xtype IN ('PK','F') AND parent_obj=OBJECT_ID('"+table+"') order by name";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    keys.Add(dataTable.Rows[i][0].ToString());
                }

                return keys;
            }
            return null;
        }

        public List<string> GetIndexes(string table)
        {
            List<string> indexes = new List<string>();
            string sql = "exec sp_helpindex '"+table+"'";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    indexes.Add(dataTable.Rows[i][0].ToString());
                }

                return indexes;
            }
            return null;
        }

        public List<string> GetTriggers(string table)
        {
            List<string> triggers = new List<string>();
            string sql = "exec sp_helptrigger '"+table+"'";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    triggers.Add(dataTable.Rows[i][0].ToString());
                }

                return triggers;
            }
            return null;
        }

        public List<string> GetConstraints(string table)
        {
            List<string> constraints = new List<string>();
            string sql = "EXEC sys.sp_helpconstraint @objname = N'" + table + "', @nomsg = 'nomsg'";
            DataTable dataTable = ExecuteSelectSql(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    constraints.Add(dataTable.Rows[i][1].ToString());
                }

                return constraints;
            }
            return null;
        }

        public List<string > GetKeywords()
        {
            List<string > keywords=new List<string>();
            string sql = "select keyword from SAP.dbo.keyword";
            DataTable dt = ExecuteSelectSql(sql);
            if (dt!=null && dt.Rows.Count>0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    keywords.Add(dt.Rows[i][0].ToString());
                }

                return keywords;
            }

            return null;
        }

        public List<string> GetSchemas(string server,string username,string password,bool istrustconnect)
        {
            List<string> schemas=new List<string>();

            string sql = "select name from master..sysdatabases order by name";

            DataTable dataTable=ExecuteSelectSql(sql);

            if (dataTable!=null && dataTable.Rows.Count>0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    schemas.Add(dataTable.Rows[i][0].ToString());
                }

                return schemas;                
            }
            return null;

        }


        public List<string> ChangeSchemas()
        {
            List<string> schemas = new List<string>();

            DataTable dataTable = base.GetSchemas();

            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    schemas.Add(dataTable.Rows[i][0].ToString());
                }

                return schemas;
            }
            return null;

        }


        public DataTable GetQueryResult(string sql)
        {
            DataTable dt = ExecuteSelectSql(sql);
            if (dt!=null)
            {
                return dt;
            }
            return null;
        }

        public string GetCustomQueryData(DataGridViewX dataGridView,string sql)
        {
            string result = ExecuteCustomSql(dataGridView, sql);
            return result;
        }

        public void GetCustomQueryData(TextBoxX txtResult, DevComponents.DotNetBar.SuperTabControl tabControl, string sql)
        {
            ExecuteCustomSql(txtResult,tabControl,sql);
        }
    }
}
